In the previous section we looked at auto-commit mode. In this mode each SQL statement is treated as a transaction. Sometimes we want to make several changes to a database and we want all, or none, of the changes to be performed together. In this case we may need to perform several SQL statements together towards the database.

In this example we will move money from one account to another through two SQL statements:

UPDATE ACCOUNTS SET BALANCE=BALANCE-10 WHERE ACCOUNTID = 1203 AND BALANCE >= 10
UPDATE ACCOUNTS SET BALANCE=BALANCE+10 WHERE ACCOUNTID = 132

In an actual application we would use host variables for the amount of money we are transferring and also for the account numbers.

using (MimerConnection conn = new MimerConnection(connectionString))
{
     conn.Open();
     //
     // The following method call starts a transaction
     //
     MimerTransaction tr = conn.BeginTransaction();
     using (MimerCommand c1 = new MimerCommand("UPDATE ACCOUNTS SET BALANCE=BALANCE-10" +
                                                                               "WHERE ACCOUNTID = 1203 AND BALANCE > 10", conn))
     {
         using (MimerCommand c2 = new MimerCommand("UPDATE ACCOUNTS SET BALANCE=BALANCE-10 " +
                                                                                   "WHERE ACCOUNTID = 1203'", conn))
         {
             //
              // The following method calls updates the two accounts
             //
             int rows1 = c1.ExecuteNonQuery();
             int rows2 = c2.ExecuteNonQuery();
             if  (rows1 == 1 && rows2 == 1)
             {
                  //
                  // We modified exactly two accounts, commit the changes to the database
                  //
                  tr.Commit();
             }
             else
             {
                  //
                  // An account id does not exist or not enough money in account, roll back any changes
                  //
                  tr.Rollback();
             }
         }
     }
}

In the above code we modify two accounts. If we are able to subtract the amount from one account and add it to another we commit the transaction. If we are not happy with the changes made the transaction is rolled back. This will also happen if there is an exception in which case the transaction is automatically rolled back when the connection goes out of scope of the using-clause.